package cn.bocweb.spinning.view.common;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

import cn.bocweb.spinning.model.Bean.DownloadFirstBean;

public class DBHelper extends SQLiteOpenHelper {
    private final static int VERSION = 1;
    private final static String DB_NAME = "spinningschool.db";
    private final static String TABLE_NAME = "secondmatch";
    private final static String TABLE_NAME2 = "configtable";
    private final static String CREATE_TBL = "CREATE TABLE IF NOT EXISTS secondmatch (id integer PRIMARY KEY autoincrement, questionID text NOT NULL, title text NOT NULL," +
            "right blob NOT NULL, explain text, option blob NOT NULL, ttype text NOT NULL, isright integer ," +
            " choose blob , libtype text NOT NULL);";
    private final static String CREATE_TBL2 = "CREATE TABLE IF NOT EXISTS configtable (id integer PRIMARY KEY autoincrement, libtype text NOT NULL, timeline text NOT NULL, " +
            "type text NOT NULL,examtitle text NOT NULL,snum text NOT NULL, dnum text NOT NULL, jnum text NOT NULL, isdatika text NOT NULL, ishuadong text NOT NULL, ischange text NOT NULL," +
            "spoint text NOT NULL, dpoint text NOT NULL, jpoint textL NOT NULL,require text NOT NULL, timelimit text NOT NULL, start text NOT NULL, end text NOT NULL, myorder text NOT NULL);";
    private SQLiteDatabase db;


    //SQLiteOpenHelper子类必须要的一个构造函数
    public DBHelper(Context context, String name, CursorFactory factory, int version) {
        //必须通过super 调用父类的构造函数
        super(context, name, factory, version);
    }

    //数据库的构造函数，传递三个参数的
    public DBHelper(Context context, String name, int version) {
        this(context, name, null, version);
    }

    //数据库的构造函数，传递一个参数的， 数据库名字和版本号都写死了
    public DBHelper(Context context) {

        this(context, DB_NAME, null, VERSION);
    }

    // 回调函数，第一次创建时才会调用此函数，创建一个数据库
    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;
        db.execSQL(CREATE_TBL);
        db.execSQL(CREATE_TBL2);
    }

    //回调函数，当你构造DBHelper的传递的Version与之前的Version调用此函数
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("DROP TABLE IF EXISTS secondmatch");
        db.execSQL("DROP TABLE IF EXISTS configmable");
        onCreate(db);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {

        super.onOpen(db);


    }

    //插入方法
    public void insert(ContentValues values, int tableName) {
        //获取SQLiteDatabase实例
        SQLiteDatabase db = getWritableDatabase();
        //插入数据库中
        String table;
        if (tableName == 1) {
            table = TABLE_NAME;
        } else if (tableName == 2) {
            table = TABLE_NAME2;
        } else {
            table = TABLE_NAME2;
        }
        db.insert(table, null, values);
        db.close();
    }

    //查询方法
    public Cursor query1(int tableName) {
        SQLiteDatabase db = getReadableDatabase();
        //获取Cursor
        String table;
        if (tableName == 1) {
            table = TABLE_NAME;
        } else if (tableName == 2) {
            table = TABLE_NAME2;
        } else {
            table = TABLE_NAME;
        }
        Cursor c = db.query(table, null, null, null, null, null, null, null);
        return c;

    }

    //查询方法
    public Cursor query2(int tableName, String libtype, String ttype, String num) {
        SQLiteDatabase db = getReadableDatabase();
        //获取Cursor
        String table;
        if (tableName == 1) {
            table = TABLE_NAME;
        } else if (tableName == 2) {
            table = TABLE_NAME2;
        } else {
            table = TABLE_NAME;
        }
        String[] str = {libtype, ttype};
        Cursor c = db.query(table, null, "libtype = ? and ttype = ?", str, null, null, "RANDOM()", num);
        return c;

    }

    //查询方法
    public Cursor query3(String libtype, String ttype, String num) {
        SQLiteDatabase db = getReadableDatabase();
        String[] str = {libtype, ttype, num};
        Cursor c = db.rawQuery("select * from secondmatch where libtype=? and ttype =?limit?", str);
        return c;

    }

    //查询libtype
    public Cursor query4(String libtype, String num) {
        SQLiteDatabase db = getReadableDatabase();
        String[] str = {libtype};
        Cursor c = db.query(TABLE_NAME, null, "libtype = " + libtype, null, null, null, null, null);
        return c;

    }

    //根据唯一标识_id  来删除数据
    public void delete(String libtype, int tableName) {

        String table;
        if (tableName == 1) {
            table = TABLE_NAME;
        } else if (tableName == 2) {
            table = TABLE_NAME2;
        } else {
            table = TABLE_NAME;
        }
        SQLiteDatabase db = getWritableDatabase();
        final String[] str = {libtype};
        db.delete(table, "libtype =?", str);

        db.close();
    }

    public void deletes(int tableName, String para, String libtype) {

        String table;
        if (tableName == 1) {
            table = TABLE_NAME;
        } else if (tableName == 2) {
            table = TABLE_NAME2;
        } else {
            table = TABLE_NAME;
        }
        String[] str = {libtype};
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("delete from secondmatch where libtype = " + libtype);
        db.execSQL("DELETE FROM configTable where libtype = " + libtype);
        db.close();
    }


    //更新数据库的内容
    public void update(ContentValues values, String whereClause, String[] whereArgs, int tableName) {
        String table;
        if (tableName == 1) {
            table = TABLE_NAME;
        } else if (tableName == 2) {
            table = TABLE_NAME2;
        } else {
            table = TABLE_NAME;
        }
        SQLiteDatabase db = getWritableDatabase();
        db.update(table, values, whereClause, whereArgs);
        db.close();
    }

    //关闭数据库
    public void close() {
        if (db != null) {
            db.close();
        }
    }

    public void changeData(String sql) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL(sql);
        db.close();
    }

    /**
     * 使用Sql语句  进行查的操作
     */
    public Cursor queryData(String sql, String[] selectionArgs) {
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.rawQuery(sql, selectionArgs);
        db.close();
        return cursor;
    }

    /**
     * 使用Api中提供的insert方法，添加数据
     *
     * @param table
     * @param values
     */
    public long ApiInsertData(String table, ContentValues values) {
        SQLiteDatabase db = getWritableDatabase();
        long flag = db.insert(table, null, values);
        db.close();
        return flag;
    }

    /**
     * 使用Api中update方法  ，修改数据
     *
     * @param table
     * @param values
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public int ApiUpdateData(String table, ContentValues values, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = getWritableDatabase();
        int flag = db.update(table, values, whereClause, whereArgs);
        db.close();
        return flag;

    }

    /**
     * 使用Api中delete方法 ，删除数据
     *
     * @param table
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public int ApiDeleteData(String table, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = getWritableDatabase();
        int flag = db.delete(table, whereClause, whereArgs);
        db.close();
        return flag;
    }

    /**
     * 使用Api中的query方法，查询数据
     *
     * @param table
     * @param columns
     * @param selection
     * @param selectionArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @return
     */
    public Cursor ApiQueryData(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
        db.close();
        return cursor;
    }


}